package org.wtframework.platform.server.util;


import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Sheet;
import jxl.Workbook;

import org.openredhead.springx.core.server.util.RegexUtils;
import org.wtframework.platform.server.manager.impl.GroupManager;
import org.wtframework.platform.server.manager.impl.UserIdentityManager;
import org.wtframework.platform.server.manager.impl.UserManager;
import org.wtframework.platform.server.model.impl.Group;
import org.wtframework.platform.server.model.impl.UserIdentity;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;


public class ImportExcelUtil {
	
	private static String emailRegx = "^\\w+([-+.]\\w+)*@\\w+([-.]\\w+)*\\.\\w+([-.]\\w+)*$";
	
	public static Object importUserExcel(File inputFile, UserManager userManager,UserIdentityManager userIdentityManager, GroupManager groupManager){
		
		List<String> errorList = new ArrayList<String>();
		
		try{
			
			String fileName = inputFile.getAbsolutePath();
			String fileType = fileName.substring(fileName.lastIndexOf(".")+1,fileName.length()).toLowerCase();
			
			if(!"xls".equals(fileType)){
				throw new Exception("请导入2003版excel！");
			}
			
			Workbook book = Workbook.getWorkbook(inputFile);
			Sheet sheet = book.getSheet(0);
			
			if(sheet == null) {
				throw new Exception("导入模板有误！");
			}
			int fristRow = sheet.getRow(0).length;
			int rowNum = sheet.getRows();
			
			if (rowNum <=0 || fristRow <= 0) {
				throw new Exception("导入模板有误！");
			}
			
			Map<String, Integer> headerMap = new HashMap<String, Integer>();
			String content = "";
			for (int i=0; i<fristRow; i++) {
				content = sheet.getCell(i, 0).getContents();
				if (!"".equals(content))
					headerMap.put(content, i);
			}
			List<Object> userMapList = new ArrayList<Object>();
			Map<String, Boolean> usernameMap = Maps.newHashMap();
			Map<String, Integer> identityMap = Maps.newHashMap();
			Map<String, Integer> groupMap = Maps.newHashMap();
			
			for (int row =1; row < rowNum; row++) {
				
				Map<String, Object> userMap = new HashMap<String, Object>();
				try {
				String username = "";
				if (headerMap.containsKey("账号"))
					username = sheet.getCell(headerMap.get("账号"), row).getContents().trim();
				String nickname = "";
				if (headerMap.containsKey("姓名")) 
					nickname = sheet.getCell(headerMap.get("姓名"), row).getContents().trim();
				
				String gender = "";
				if (headerMap.containsKey("性别")) 
					gender = sheet.getCell(headerMap.get("性别"), row).getContents().trim();
				
				String phone = "";
				if (headerMap.containsKey("手机号码")) 
					phone = sheet.getCell(headerMap.get("手机号码"), row).getContents().trim();
				
				String email = "";
				if (headerMap.containsKey("E-mail")) 
					email = sheet.getCell(headerMap.get("E-mail"), row).getContents().trim();
				
				String identify = "";
				if (headerMap.containsKey("身份")) 
					identify = sheet.getCell(headerMap.get("身份"), row).getContents().trim();
				
				//用户身份
				Integer identifyId = null;
				Double score = 0.0;
				if(!Strings.isNullOrEmpty(identify)){
					identifyId = identityMap.get(identify);
					if(identifyId == null) {
						UserIdentity userIdentity = userIdentityManager.findUnique("name", identify);
						if(userIdentity == null) {
							errorList.add("第"+(row+1)+"行记录出错了,错误原因：身份‘"+identify+"’不存在");
							identityMap.put(identify, -1);
							continue ;
						}
						
						identifyId = userIdentity.getId();
						score = userIdentity.getInitScore();
						identityMap.put(identify, identifyId);
					}else if(identifyId == -1) {
						errorList.add("第"+(row+1)+"行记录出错了,错误原因：身份‘"+identify+"’不存在");
						continue ;
					} 
				}
				
				
				String groupName = "";
				if (headerMap.containsKey("部门/班级")) 
					groupName = sheet.getCell(headerMap.get("部门/班级"), row).getContents().trim();
				
				//用户身份
				Integer groupId = null;
				if(!Strings.isNullOrEmpty(groupName)){
					groupId = groupMap.get(groupName);
					if(groupId == null) {
						Group group = groupManager.findUnique("name", groupName);
						if(group == null) {
							errorList.add("第"+(row+1)+"行记录出错了,错误原因：部门/班级‘"+groupName+"’不存在");
							groupMap.put(groupName, -1);
							continue ;
						}
						
						groupId = group.getId();
						groupMap.put(groupName, groupId);
					}else if(identifyId == -1) {
						errorList.add("第"+(row+1)+"行记录出错了,错误原因：部门/班级‘"+groupName+"’不存在");
						continue ;
					} 
				}
				
				
				
				if(username == ""){
					//errorList.add("第"+(row+1)+"行记录出错了,错误原因：空的教工号！");
					continue ;
				}
				
				if(Strings.isNullOrEmpty(nickname)){
					errorList.add("第"+(row+1)+"行记录出错了,错误原因：空的用户姓名！");
					continue ;
				}
				if(Strings.isNullOrEmpty(gender)){
					errorList.add("第"+(row+1)+"行记录出错了,错误原因：空的用户性别！");
					continue ;
				}
				
				if(Strings.isNullOrEmpty(email)){
					errorList.add("第"+(row+1)+"行记录出错了,错误原因：空的邮箱地址！");
					continue ;
				}
				if(!Strings.isNullOrEmpty(email) && !RegexUtils.isRegexMatch(emailRegx, email)){
					errorList.add("第"+(row+1)+"行记录出错了,错误原因：错误的邮箱地址格式！");
					continue;
				}
				
				if(usernameMap.containsKey(username)) {
					errorList.add("第"+(row+1)+"行记录出错了,错误原因：重复导入用户！");
					continue;
				}
				usernameMap.put(username, true);
				if(userManager.find("username", username).getCount() > 0) {
					errorList.add("第"+(row+1)+"行记录出错了,错误原因：用户名已存在！");
					continue;
				}
				userMap.put("username", username);
				userMap.put("nickname", nickname);
				userMap.put("gender", gender);
				userMap.put("email", email);
				userMap.put("phone", phone);
				userMap.put("identify", identifyId);
				userMap.put("score", score);
				userMap.put("groupId", groupId);
				} catch(Exception e){
					e.printStackTrace();
					errorList.add("第"+(row+1)+"行记录出错了,错误原因：未知错误！");
					continue ;
				}
				userMapList.add(userMap);
				
			}
			userMapList.add(errorList);
			return userMapList;
			
		}catch(Exception e){
			return e.getMessage();
		}
	}
}
